// 文件名称: ClassManagementController.java
// 创建日期: 2023-10-05
// 描述: 班级管理控制器类，负责处理班级管理界面的各种操作。

package org.wisdom.studentchoose;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.Stage;
import org.wisdom.studentchoose.db.ClassInfo;
import org.wisdom.studentchoose.db.DatabaseConnection;
import javax.swing.JOptionPane;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassManagementController {

    @FXML
    private TextField searchClassField; // 搜索班级输入框

    @FXML
    private TextField addClassField; // 添加班级输入框

    @FXML
    private TextField deleteClassField; // 删除班级输入框

    @FXML
    private TableView<ClassInfo> classList; // 班级列表表格

    @FXML
    private TableColumn<ClassInfo, Integer> classIdColumn; // 班级ID列

    @FXML
    private TableColumn<ClassInfo, String> classNameColumn; // 班级名称列

    @FXML
    private Button backButton; // 返回按钮

    private ObservableList<ClassInfo> classData = FXCollections.observableArrayList(); // 班级数据列表

    @FXML
    public void initialize() {
        // 初始化表格列
        classIdColumn.setCellValueFactory(new PropertyValueFactory<>("id")); // 设置班级ID列的数据源
        classNameColumn.setCellValueFactory(new PropertyValueFactory<>("name")); // 设置班级名称列的数据源

        // 加载班级列表
        loadClassList();
    }

    private void loadClassList() {
        // 清空班级数据列表
        classData.clear();

        // 从数据库中获取班级列表
        List<ClassInfo> classes = fetchClassesFromDatabase();

        // 将班级列表添加到可观察列表中
        classData.addAll(classes);

        // 设置表格数据
        classList.setItems(classData);
    }

    private List<ClassInfo> fetchClassesFromDatabase() {
        // 创建班级列表
        List<ClassInfo> classes = new ArrayList<>();

        try (Connection connection = DatabaseConnection.getConnection()) {
            // 准备SQL查询语句
            PreparedStatement statement = connection.prepareStatement("SELECT * FROM Class");

            // 执行查询
            ResultSet resultSet = statement.executeQuery();

            // 遍历查询结果，创建班级对象并添加到列表中
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                classes.add(new ClassInfo(id, name));
            }
        } catch (SQLException e) {
            // 打印异常堆栈信息
            e.printStackTrace();
        }

        return classes;
    }

    private List<ClassInfo> searchClassesInDatabase(String searchName) {
        // 创建班级列表
        List<ClassInfo> classes = new ArrayList<>();

        try (Connection connection = DatabaseConnection.getConnection()) {
            // 准备SQL查询语句
            PreparedStatement statement = connection.prepareStatement("SELECT * FROM Class WHERE name LIKE ?");

            // 设置查询条件
            statement.setString(1, "%" + searchName + "%");

            // 执行查询
            ResultSet resultSet = statement.executeQuery();

            // 遍历查询结果，创建班级对象并添加到列表中
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                classes.add(new ClassInfo(id, name));
            }
        } catch (SQLException e) {
            // 打印异常堆栈信息
            e.printStackTrace();
        }

        return classes;
    }

    @FXML
    private void handleAddButtonClick(ActionEvent event) {
        // 获取输入的班级名称
        String className = addClassField.getText().trim();

        // 检查输入是否为空
        if (!className.isEmpty()) {
            // 将班级添加到数据库
            addClassToDatabase(className);

            // 重新加载班级列表
            loadClassList();

            // 清空输入框
            addClassField.clear();
        }
    }

    private void addClassToDatabase(String className) {
        try (Connection connection = DatabaseConnection.getConnection()) {
            // 检查班级名称是否已存在
            String checkClassSql = "SELECT COUNT(*) FROM Class WHERE name = ?";
            try (PreparedStatement checkClassStmt = connection.prepareStatement(checkClassSql)) {
                checkClassStmt.setString(1, className);
                try (ResultSet resultSet = checkClassStmt.executeQuery()) {
                    if (resultSet.next() && resultSet.getInt(1) > 0) {
                        // 班级名称已存在，弹出提示框报错
                        JOptionPane.showMessageDialog(null, "班级名称已存在: " + className, "错误", JOptionPane.ERROR_MESSAGE);
                        return;
                    }
                }
            }

            // 插入新的班级
            String insertClassSql = "INSERT INTO Class (name) VALUES (?)";
            try (PreparedStatement insertClassStmt = connection.prepareStatement(insertClassSql)) {
                insertClassStmt.setString(1, className);
                insertClassStmt.executeUpdate();
            }
        } catch (SQLException e) {
            // 打印异常堆栈信息
            e.printStackTrace();
        }
    }

    @FXML
    private void handleDeleteButtonClick(ActionEvent event) {
        // 获取输入的班级名称
        String className = deleteClassField.getText().trim();

        // 检查输入是否为空
        if (!className.isEmpty()) {
            // 从数据库中删除班级及其相关数据
            deleteClassFromDatabase(className);

            // 重新加载班级列表
            loadClassList();

            // 清空输入框
            deleteClassField.clear();
        }
    }

    private void deleteClassFromDatabase(String className) {
        try (Connection connection = DatabaseConnection.getConnection()) {
            // 删除学生表中对应班级的学生
            String deleteStudentsSql = "DELETE FROM students WHERE class_name = ?";
            try (PreparedStatement deleteStudentsStmt = connection.prepareStatement(deleteStudentsSql)) {
                deleteStudentsStmt.setString(1, className);
                deleteStudentsStmt.executeUpdate();
            }

            // 删除小组表中对应班级的小组
            String deleteGroupsSql = "DELETE FROM `groups` WHERE class_name = ?";
            try (PreparedStatement deleteGroupsStmt = connection.prepareStatement(deleteGroupsSql)) {
                deleteGroupsStmt.setString(1, className);
                deleteGroupsStmt.executeUpdate();
            }

            // 删除班级表中的班级
            String deleteClassSql = "DELETE FROM `class` WHERE name = ?";
            try (PreparedStatement deleteClassStmt = connection.prepareStatement(deleteClassSql)) {
                deleteClassStmt.setString(1, className);
                deleteClassStmt.executeUpdate();
            }
        } catch (SQLException e) {
            // 打印异常堆栈信息
            e.printStackTrace();
        }
    }

    @FXML
    private void handleBackButtonClick(ActionEvent event) {
        try {
            // 加载主菜单界面的 FXML 文件
            FXMLLoader fxmlLoader = new FXMLLoader(HelloApplication.class.getResource("menu-view.fxml"));
            Scene scene = new Scene(fxmlLoader.load(), 400, 300); // 设置主菜单界面的大小
            Stage stage = (Stage) ((Button) event.getSource()).getScene().getWindow();
            stage.setScene(scene);
            stage.show();
        } catch (IOException e) {
            // 打印异常堆栈信息
            e.printStackTrace();
        }
    }
}
